﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.XPath;

namespace Common.Utilities.OpenXml
{
	/// <summary>
	/// Encapsulates the functionality of a worksheet.
	/// </summary>
	public class Worksheet : PackagePartBase
	{
		private Worksheet(PackageHelper package, Uri uri)
			: base(package, uri)
		{ }

		/// <summary>
		/// Opens an existing worksheet part from the package.
		/// </summary>
		/// <param name="package">The package containing the worksheet part</param>
		/// <param name="name">The name of the new part to create.</param>
		/// <returns>A new instance of a Worksheet object.</returns>
		public static Worksheet Open(PackageHelper package, string name)
		{
			// create the uri
			Uri uri = new Uri(string.Format("/xl/worksheets/{0}.xml", name), UriKind.Relative);

			// check if the part exists
			if (package.PartExists(uri))
				return new Worksheet(package, uri);
			else
				return null;
		}

		/// <summary>
		/// Adds a pivot table to the sheet.
		/// </summary>
		/// <param name="pivotTable">The PivotTable object to add.</param>
		public void AddPivotTable(PivotTable pivotTable)
		{
			// create the relationship
			base.Package.CreateInternalRelationship(base.Uri,
			pivotTable.Uri,
				"http://schemas.openxmlformats.org/officeDocument/" +
				"2006/relationships/pivotTable");
		}

		/// <summary>
		/// Adds a chart to the sheet.
		/// </summary>
		/// <param name="chart">The Chart object to add.</param>
		public void AddChart(Chart chart)
		{
			// create the relationship
			string relId =
				base.Package.CreateInternalRelationship(base.Uri,
					chart.Uri,
					"http://schemas.openxmlformats.org/officeDocument/" +
					"2006/relationships/drawing");

			// add the drawing to the spreadsheet
			XPathNavigator worksheetNav =
				PackageData.DocumentElement.CreateNavigator();
			using (XmlWriter writer = worksheetNav.AppendChild())
			{
				// <drawing r:id="rId1" />
				writer.WriteStartElement("drawing",
					Namespaces.SpreadsheetML);
				writer.WriteAttributeString(Prefixes.Relationships, "id",
					Namespaces.Relationships, relId);
				writer.WriteEndElement();
			}
		}

		/// <summary>
		/// Adds data bar conditional formatting to a column of cells.
		/// </summary>
		/// <param name="column">The column to apply the formatting to.</param>
		/// <param name="startRow">The start row number to apply the formatting to.</param>
		/// <param name="endRow">The end row number to apply the formatting to.</param>
		public void AddConditionalFormatting(string column, int startRow, int endRow)
		{
			// add the conditional formatting element
			XPathNavigator sheetDataNav =
				PackageData.CreateNavigator().SelectSingleNode(
				"x:worksheet/x:sheetData", Namespaces.NamespaceManager);
			using (XmlWriter writer = sheetDataNav.InsertAfter())
			{
				// <conditionalFormatting pivot="1" sqref="B2:B11">
				writer.WriteStartElement("conditionalFormatting",
					Namespaces.SpreadsheetML);
				writer.WriteAttributeString("pivot", "1");
				writer.WriteAttributeString("sqref",
					string.Format("{0}{1}:{0}{2}", column,
						startRow.ToString(), endRow.ToString()));

				// <cfRule type="dataBar" priority="1">
				writer.WriteStartElement("cfRule",
					Namespaces.SpreadsheetML);
				writer.WriteAttributeString("type", "dataBar");
				writer.WriteAttributeString("priority", "1");

				// <dataBar>
				writer.WriteStartElement("dataBar",
					Namespaces.SpreadsheetML);

				// <cfvo type="min" val="0" />
				writer.WriteStartElement("cfvo",
					Namespaces.SpreadsheetML);
				writer.WriteAttributeString("type", "min");
				writer.WriteAttributeString("val", "0");
				writer.WriteEndElement();

				// <cfvo type="max" val="0" />
				writer.WriteStartElement("cfvo",
					Namespaces.SpreadsheetML);
				writer.WriteAttributeString("type", "max");
				writer.WriteAttributeString("val", "0");
				writer.WriteEndElement();

				// <color rgb="FF638EC6" />
				writer.WriteStartElement("color",
					Namespaces.SpreadsheetML);
				writer.WriteAttributeString("rgb", "FF638EC6");
				writer.WriteEndElement();

				// </dataBar>
				// </cfRule>
				// </conditionalFormatting>
				writer.WriteEndElement();
				writer.WriteEndElement();
				writer.WriteEndElement();
			}
		}
	}
}
